﻿using System.Data;
using System;
using System.Data.Odbc;

namespace crm.model
{
    public class UsuarioDao : Conexao
    {
        Conexao c = new Conexao();


        public bool inserir(Usuario obj)
        {
            colParams.Clear();
            string query = "insert into usuarios (nome,email,senha,fkidstatus) values ( ?, ?, ?, ?);";
            colParams.Add(new OdbcParameter("@nome", obj.Nome));
            colParams.Add(new OdbcParameter("@email", obj.Email));
            colParams.Add(new OdbcParameter("@senhas", obj.Senha));
            colParams.Add(new OdbcParameter("@fkidstatus", obj.status));
            return c.Executar(query, colParams);
        }

        public DataTable consultar()
        {
            string query = "select * from vw_usuarios";
            return c.ExecutarConsulta(query);
        }

        public DataTable consultar(int id)
        {
            colParams.Clear();
            string query = "select * from usuarios where idusuarios = ? and fkidstatus=0;";
            colParams.Add(new OdbcParameter("@id", id));
            return c.ExecutarConsulta(query, colParams);
        }

        public DataTable consultar(string nome)
        {
            colParams.Clear();
            string query = "select * from usuarios where nome like  ?  and fkidstatus = 0;";
            colParams.Add(new OdbcParameter("@nome", nome));
            return c.ExecutarConsulta(query, colParams);
        }

        public bool atualizar(Usuario obj)
        {
            colParams.Clear();
            string query = "update usuario set nome = ?, email = ?, senha = ? where idusuarios = ?;";
            colParams.Add(new OdbcParameter("@nome", obj.Nome));
            colParams.Add(new OdbcParameter("@email", obj.Email));
            colParams.Add(new OdbcParameter("@id", obj.IdUsuario));
            colParams.Add(new OdbcParameter("@fkidsenhausuarios", obj.Senha));
            colParams.Add(new OdbcParameter("@idUsuarios", obj.IdUsuario));
            return c.Executar(query, colParams);
        }
        public bool remover(int id)
        {
            colParams.Clear();
            string query = "update usuarios set fkidstatus = 1 where idusuarios = ?;";
            colParams.Add(new OdbcParameter("@idusuarios", id));
            return c.Executar(query, colParams);
        }

        public DataTable login(string email, string senha)
        {
            colParams.Clear();
            string query = "select * from usuarios where fkidstatus = 0 and email like ? and senha like ?;";
            colParams.Add(new OdbcParameter("@email", email));
            colParams.Add(new OdbcParameter("@senha", senha));
            return c.ExecutarConsulta(query, colParams);

            //string query = "select * from usuarios where fkidstatus = 0 and email like '" + email + "' and senha like '" + senha + "';";
            //return c.ExecutarConsulta(query);
        }
    }
}
